insert overwrite table jms_dm.dm_agent_taking_arrival_agg_dt partition(dt = '{{ execution_date |  cst_ds }}')
select
    dt as import_date
    ,ordersource_code
    ,agent_code
    ,max(ordersource_name) as ordersource_name
    ,max(agent_name) as agent_name
    ,count(distinct waybill_no) as taking_cnt -- 收件量运单去重
    ,count(distinct if(scan_type in ('siteArrival','centerArrival'),concat(network_code,'-',waybill_no),null)) as arrival_cnt -- 到件量运单 + 网点去重
from
    (
    select dt,waybill_no,scan_type,network_code
    from jms_dwd.dwd_wide_rank_basic_scaninfo_dt
    where
        dt = '{{ execution_date |  cst_ds }}' and waybill_no is not null
        and network_code not in ('Test001','Test0001','3021888','888888')
    group by dt,waybill_no,scan_type,network_code
    ) t1
left join
    (
    select
        code
        ,max(subordinate_agent_code) as agent_code
        ,max(subordinate_agent_name) as agent_name
    from jms_dim.dim_lmdm_sys_network_expand
    where code not in ('Test001','Test0001','3021888','888888')
    group by
        code
    ) t2
on t1.network_code = t2.code
left join
    (
    select
        billcode
        ,max(ordersource_code) as ordersource_code
        ,max(ordersource_name) as ordersource_name
    from
        jms_dwd.dwd_order_source_detail_dt
    where
        dt = '{{ execution_date |  cst_ds }}'
    group by
        billcode
    ) t3
on t1.waybill_no = t3.billcode
group by
    dt
    ,ordersource_code
    ,agent_code ;